DBMS (Part 2)

*21. What is Denormalization?*

Answer:
Denormalization is the opposite of normalization.

It combines tables to reduce joins and improve query performance.

Used mainly in data warehouses and reporting systems.

━━━━━━━━━━━━━━━━━━━━

*22. What is a Join?*

Answer:
A Join is used to combine data from two or more tables based on a related column.

Types of Joins:
• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL OUTER JOIN
• SELF JOIN

━━━━━━━━━━━━━━━━━━━━

*INNER JOIN*

Returns only the matching records from both tables.

━━━━━━━━━━━━━━━━━━━━

*LEFT JOIN*

Returns all records from the left table and matching records from the right table.

If no match exists, NULL values are returned.

━━━━━━━━━━━━━━━━━━━━

*RIGHT JOIN*

Returns all records from the right table and matching records from the left table.

If no match exists, NULL values are returned.

━━━━━━━━━━━━━━━━━━━━

*FULL OUTER JOIN*

Returns all records from both tables.

If no match exists, NULL values are returned.

━━━━━━━━━━━━━━━━━━━━

*SELF JOIN*

A table is joined with itself.

Used to compare rows within the same table.

━━━━━━━━━━━━━━━━━━━━

*23. Difference Between DELETE, DROP and TRUNCATE*

DELETE
• Removes selected rows
• WHERE clause allowed
• Rollback possible
• Table structure remains

DROP
• Deletes the entire table
• WHERE clause not allowed
• Rollback not possible
• Table structure removed

TRUNCATE
• Removes all rows
• WHERE clause not allowed
• Faster than DELETE
• Table structure remains

━━━━━━━━━━━━━━━━━━━━

*24. DELETE vs TRUNCATE*

DELETE
• Deletes rows one by one
• Slower
• WHERE clause allowed

TRUNCATE
• Deletes all rows at once
• Faster
• WHERE clause not allowed

━━━━━━━━━━━━━━━━━━━━

*25. What is an Index?*

Answer:
An Index improves the speed of data retrieval.

Example:
Like the index of a book, it helps locate data quickly.

Advantages:
• Faster searching
• Better query performance

Disadvantage:
• Uses additional storage space.

━━━━━━━━━━━━━━━━━━━━

*26. What is a View?*

Answer:
A View is a virtual table created using a SELECT query.

It does not store actual data.

━━━━━━━━━━━━━━━━━━━━

*27. What is a Stored Procedure?*

Answer:
A Stored Procedure is a collection of SQL statements stored in the database.

It can be executed multiple times and improves performance.

━━━━━━━━━━━━━━━━━━━━

*28. What is a Trigger?*

Answer:
A Trigger is a set of SQL statements that automatically executes when an event occurs.

Events:
• INSERT
• UPDATE
• DELETE

━━━━━━━━━━━━━━━━━━━━

*29. What is a Cursor?*

Answer:
A Cursor processes records one row at a time.

Used when row-by-row processing is required.

━━━━━━━━━━━━━━━━━━━━

*30. What is a Transaction?*

Answer:
A Transaction is a group of SQL operations executed as a single unit.

Example:
Bank Transfer

Debit Money
↓

Credit Money

Both operations should complete successfully.

━━━━━━━━━━━━━━━━━━━━

*31. What are ACID Properties?*

Answer:
ACID properties ensure reliable database transactions.

*A – Atomicity*
All operations happen completely or not at all.

*C – Consistency*
The database remains in a valid state before and after a transaction.

*I – Isolation*
Multiple transactions do not interfere with each other.

*D – Durability*
Committed data is permanently stored even after a system failure.

Memory Trick:
A C I D

All
Consistent
Independent
Data Safe

━━━━━━━━━━━━━━━━━━━━

*32. What is Concurrency?*

Answer:
Concurrency means multiple users can access the database at the same time.

━━━━━━━━━━━━━━━━━━━━

*33. What is Locking?*

Answer:
Locking prevents multiple users from modifying the same data simultaneously.

It maintains data consistency.

━━━━━━━━━━━━━━━━━━━━

*34. What is Deadlock?*

Answer:
A Deadlock occurs when two or more transactions wait for each other indefinitely, preventing execution.

━━━━━━━━━━━━━━━━━━━━

*35. What is Data Redundancy?*

Answer:
Data Redundancy means duplicate copies of the same data are stored.

Normalization helps reduce redundancy.

━━━━━━━━━━━━━━━━━━━━

*36. What is Data Integrity?*

Answer:
Data Integrity ensures that the data stored in the database is accurate, valid, and consistent.

━━━━━━━━━━━━━━━━━━━━

*37. Types of Integrity*

• Entity Integrity
• Referential Integrity
• Domain Integrity

━━━━━━━━━━━━━━━━━━━━

*38. What is Referential Integrity?*

Answer:
Referential Integrity ensures that every Foreign Key value refers to an existing Primary Key value.

This maintains valid relationships between tables.

━━━━━━━━━━━━━━━━━━━━

*39. What is NULL?*

Answer:
NULL represents an unknown or missing value.

Remember:
• NULL ≠ 0
• NULL ≠ Empty String

━━━━━━━━━━━━━━━━━━━━

*40. Aggregate Functions*

Answer:
Aggregate functions perform calculations on multiple rows and return a single value.

Common Aggregate Functions:
• COUNT()
• SUM()
• AVG()
• MAX()
• MIN()

━━━━━━━━━━━━━━━━━━━━

*Quick Revision*

• Denormalization → Improves Performance
• JOIN → Combines Tables
• INNER JOIN → Matching Records
• LEFT JOIN → All Left + Matching Right
• RIGHT JOIN → All Right + Matching Left
• FULL JOIN → All Records
• SELF JOIN → Table with Itself
• DELETE → Remove Selected Rows
• TRUNCATE → Remove All Rows
• DROP → Delete Table
• Index → Faster Search
• View → Virtual Table
• Stored Procedure → Reusable SQL Block
• Trigger → Automatic SQL Execution
• Cursor → Row-by-Row Processing
• Transaction → Group of SQL Operations
• ACID → Atomicity, Consistency, Isolation, Durability
• Concurrency → Multiple Users Access Database
• Locking → Prevents Simultaneous Modification
• Deadlock → Transactions Waiting Forever
• Data Integrity → Accurate & Consistent Data
• Referential Integrity → Valid Foreign Key Relationships
• NULL → Unknown Value
• Aggregate Functions → COUNT, SUM, AVG, MAX, MIN